/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.FindAndSet;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import com.comut.crud.pojo.poi.Poi;
import com.comut.crud.util.Util;
import java.sql.*;
import java.util.ResourceBundle;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class PoiDao {

    private static Logger _logger = Logger.getLogger(PoiDao.class.getName());
    private Connection _connection;

    public PoiDao(Connection connection) {
        this._connection = connection;
    }


    /**
     *  @author celia calcada
     *  ...
     */
    public ResultSet getPoi(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getPoi](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getPoi error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createPoi(Poi poi) {
        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createPoi](?,?,?,?,?,?,?,?,?,?,?)}";
        try {

            stmt = _connection.prepareCall(query);
            stmt.setString(1, poi.getId_user());
            stmt.setString(2, poi.getId_group());
            stmt.setString(3, poi.getId_external());
            stmt.setString(4, poi.getId_image());
            stmt.setString(5, poi.getLatitude());
            stmt.setString(6, poi.getLongitude());
            stmt.setString(7, poi.getRadius());
            stmt.setString(8, poi.getAlias());
            stmt.setString(9, poi.getValidated());
            stmt.setString(10, poi.getParameters());
            stmt.setInt(11, poi.getId_category());
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }


        } catch (SQLException ex) {
            _logger.error("createPoi error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createPoi error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
    }

    /**
     * Update the poi table in SQLServer Also create or update [poi_group]
     *
     * @param poi Object with the where clause and the update clause
     */
    public void updatePoi(FindAndSet poi) {

        int poiId = Integer.parseInt(Util.cutFirstAndLastChars(poi.getFind().substring(poi.getFind().indexOf("=") + 1).trim()));
        String id_external = null;
        //update poi
        if ((poi.getUpdate() != null && poi.getUpdate().length() > 0) || (poi.getPoi() != null && poi.getPoi().updateXml())) {
            String update = "";
            //add updates
            if (poi.getUpdate() != null && poi.getUpdate().length() > 0) {
                /**
                 * If the id_external exists, check if is unique in group
                 */
                String poiId_external = ResourceBundle.getBundle("dbFields").getString("poiId_external");

                String[] split = poi.getUpdate().split(",");
                for (int i = 0; i < split.length; i++) {
                    String aux = split[i];
                    if (aux.contains(poiId_external)) {
                        id_external = aux.substring(aux.lastIndexOf("=") + 1).trim();
                        id_external = Util.cutFirstAndLastChars(id_external);
                        if (id_external != null) {
                            checkUniqueId_external(id_external, poiId + "");
                        }
                    }
                }



                update += poi.getUpdate();
            }

            //add xml part
            if (poi.getPoi() != null && poi.getPoi().updateXml()) {
                if (!update.equals("")) {
                    update += ", ";
                }

                update += "[" + ResourceBundle.getBundle("dbFields").getString("poiParameters") + "]='" + poi.getPoi().getParameters() + "'";
            }

            update = "UPDATE [cmt].[poi] SET " + update;
            String where = " WHERE " + poi.getFind();

            PreparedStatement stmt = null;
            try {
                stmt = _connection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The poi does not exist", ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updatePoi error", ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
        }

        //setup poi category
        //Check if the pair poi<->group already exists        
        if (poi.getPoi() != null && poi.getPoi().updatePoiCategory()) {
            ResultSet rs = null;
            CallableStatement stmt = null;
            String query = "{call [crud].[usp_updatePoiPoiCategory](?,?,?)}";
            try {

                stmt = _connection.prepareCall(query);
                stmt.setString(1, poi.getPoi().getId_group());
                stmt.setString(2, poiId + "");
                stmt.setString(3, poi.getPoi().getId_category() + "");
                stmt.execute();

            } catch (SQLException ex) {
                _logger.error("update PoiPoiCategory error", ex);
                if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                    throw new ObjectNotFoundException(ex, ex.getErrorCode());
                } else {
                    throw new BaseException(ex, ex.getErrorCode());
                }
            } catch (Exception ex) {
                _logger.error("update error", ex);
                throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException ignore) {
                    }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
        }



    }

    /**
     * delete poi with the ID id, delete from [cmt].[poi_group] and [cmt].[poi]
     *
     * @param id
     */
    public void deletePoi(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deletePoi](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deletePoi error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectNotFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     * check if there is other poi with the id_external in the same group, if
     * exists throw an exception
     *
     * @param id_external
     * @param poi_id
     */
    private void checkUniqueId_external(String id_external, String poi_id) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueId_external](?,?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setString(1, id_external);
            stmt.setString(2, poi_id);

            rs = stmt.executeQuery();
            if (rs.next()) {
                throw new ObjectFoundException("The id_external already exists");
            }
        } catch (SQLException ex) {
            _logger.error("checkUniqueId_external error", ex);
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }

    /**
     * get the parameters (XML field)
     *
     * @param poi_id
     * @return
     */
    public String getParameters(String poi_id) {
        ResultSet rs = null;
        String parameters = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getParameters](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setString(1, poi_id);

            rs = stmt.executeQuery();
            if (rs.next()) {
                parameters = rs.getString(1);
            }
        } catch (SQLException ex) {
            _logger.error("getParameters error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return parameters;

    }

    /**
     * Issue: IFL-1568
     * @author celia calcada
     * @param userId
     * @return
     */
    public ResultSet getPOIsForUserId(int userId) {
        ResultSet resultSet = null;
        CallableStatement statement = null;
        String query = "{call [crud].[usp_getPOIsForUserId](?)}";

        try {
            statement = _connection.prepareCall(query);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getPOIsForUserId error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return resultSet;
    }
}
